﻿// ===============================================================================
// MSSQLSchemaProvider.cs
// EOS Framework
// ===============================================================================
// Copyright (c) EOS Development Group. All rights reserved.
// ===============================================================================

namespace EOS.Framework.Schema.MSSQL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using EOS.Framework.Contracts.Schema;

    /// <summary>
    /// Extracts schema information from a MSSQL database.
    /// </summary>
    public class MSSQLSchemaProvider : ISchemaProvider
    {
        /// <summary>
        /// Reference to the sqlConnection object.
        /// </summary>
        private SqlConnection sqlConnection;
        /// <summary>
        /// Dictionary of all sql type names and their corresponding Enumeration Value.
        /// </summary>
        private static Dictionary<string, SqlType> sqlTypes;
        /// <summary>
        /// The cached Schema
        /// </summary>
        private DatabaseSchema cachedSchema;

        /// <summary>
        /// Initializes a new instance of the <see cref="MSSQLSchemaProvider"/> class
        /// by using an existing sql connection.
        /// </summary>
        /// <param name="sqlConnection">The SQL connection. Cannot be null.</param>
        /// <exception cref="ArgumentNullException"><paramref name="sqlConnection"/> is
        /// null (Nothing in Visual Basic).</exception>
        public MSSQLSchemaProvider(SqlConnection sqlConnection)
        {
            if (sqlConnection == null)
            {
                throw new ArgumentNullException("sqlConnection");
            }

            this.sqlConnection = sqlConnection;

            BuildSqlTypesDictionary();
        }

        /// <summary>
        /// Builds the SQL types dictionary.
        /// </summary>
        private static void BuildSqlTypesDictionary()
        {
            if (sqlTypes == null)
            {
                sqlTypes = new Dictionary<string, SqlType>();

                sqlTypes.Add("bit", SqlType.Boolean);
                sqlTypes.Add("char", SqlType.AnsiChar);
                sqlTypes.Add("nvarchar", SqlType.VarChar);
                sqlTypes.Add("nvarchar(max)", SqlType.VarCharMax);
                sqlTypes.Add("text", SqlType.AnsiText);
                sqlTypes.Add("sysname", SqlType.VarChar);
                sqlTypes.Add("ntext", SqlType.Text);
                sqlTypes.Add("nchar", SqlType.Char);
                sqlTypes.Add("varchar", SqlType.AnsiVarChar);
                sqlTypes.Add("varchar(max)", SqlType.AnsiVarCharMax);
                sqlTypes.Add("binary", SqlType.Binary);
                sqlTypes.Add("varbinary", SqlType.VarBinary);
                sqlTypes.Add("datetime", SqlType.DateTime);
                sqlTypes.Add("decimal", SqlType.Decimal);
                sqlTypes.Add("numeric", SqlType.Decimal);
                sqlTypes.Add("float", SqlType.Double);
                sqlTypes.Add("real", SqlType.Float);
                sqlTypes.Add("smalldatetime", SqlType.SmallDateTime);
                sqlTypes.Add("tinyint", SqlType.Byte);
                sqlTypes.Add("smallint", SqlType.Int16);
                sqlTypes.Add("int", SqlType.Int32);
                sqlTypes.Add("int identity", SqlType.Int32);
                sqlTypes.Add("uniqueidentifier", SqlType.GUID);
                sqlTypes.Add("bigint", SqlType.Int64);
                sqlTypes.Add("image", SqlType.Image);
                sqlTypes.Add("varbinary(max)", SqlType.VarBinaryMax);
                sqlTypes.Add("money", SqlType.Money);
                sqlTypes.Add("smallmoney", SqlType.SmallMoney);
                sqlTypes.Add("timestamp", SqlType.Timestamp);
                sqlTypes.Add("unknown", SqlType.Unknown);
            }
        }

        #region ISchemaProvider Members

        /// <summary>
        /// Extracts the schema from the database.
        /// </summary>
        /// <param name="forceNew">if set to <c>true</c> the provider will be forced to extract the schema again.</param>
        /// <returns>The extracted schema.</returns>
        public DatabaseSchema ExtractSchema(bool forceNew)
        {
            // Check if we havent extracted the schema information yet or 
            // if we are foreced to extract it again.
            if (cachedSchema == null || forceNew)
            {
                cachedSchema = new DatabaseSchema(sqlConnection.ConnectionString);

                // Get the list of tables from the sql connection and loop
                // through all tables.
                using (DataTable schemaTable = sqlConnection.GetSchema("TABLES"))
                {
                    foreach (DataRow row in schemaTable.Rows)
                    {
                        // Create a table schema object for each table and fill it
                        // with the schema name, the catalog and the table name.
                        TableSchema tableSchema = new TableSchema();
                        tableSchema.Name = (string)row["TABLE_NAME"];
                        tableSchema.Schema = (string)row["TABLE_CATALOG"];
                        tableSchema.Catalog = (string)row["TABLE_SCHEMA"];

                        using (SqlCommand tableCommand = new SqlCommand(string.Format(@"Select * From {0} Where 0=1", tableSchema.Name), sqlConnection))
                        {
                            using (SqlDataReader reader = tableCommand.ExecuteReader())
                            {
                                // Get the schema information for a specifiy table from the sqlconnection
                                // and loop through all columns.
                                using (DataTable tableSchemaTable = reader.GetSchemaTable())
                                {
                                    foreach (DataRow columnRow in tableSchemaTable.Rows)
                                    {
                                        // Create a column schema object for each
                                        // column and fill it with the data.
                                        ColumnSchema columnSchema = new ColumnSchema();
                                        columnSchema.Name = (string)columnRow["ColumnName"];
                                        columnSchema.AllowNull = (bool)columnRow["AllowDBNull"];
                                        columnSchema.IsIdentity = (bool)columnRow["IsIdentity"];
                                        columnSchema.DataType = (Type)columnRow["DataType"];

                                        // Try to find out the SqlType
                                        // for the current column.
                                        string sqlTypeName = (string)columnRow["DataTypeName"];
                                        if (sqlTypes.ContainsKey(sqlTypeName))
                                        {
                                            columnSchema.SqlDataType = sqlTypes[sqlTypeName];
                                        }
                                        else
                                        {
                                            columnSchema.SqlDataType = SqlType.Unknown;
                                        }

                                        // If the column data type is some kind of a string
                                        // or a binary saved data, extract the length information.
                                        if (columnSchema.SqlDataType == SqlType.Char ||
                                            columnSchema.SqlDataType == SqlType.AnsiChar ||
                                            columnSchema.SqlDataType == SqlType.VarChar ||
                                            columnSchema.SqlDataType == SqlType.AnsiVarChar ||
                                            columnSchema.SqlDataType == SqlType.Binary ||
                                            columnSchema.SqlDataType == SqlType.VarBinary)
                                        {
                                            columnSchema.Length = (int)columnRow["ColumnSize"];
                                        }
                                        // If it is a decimal, extract precision and scale.
                                        else if (columnSchema.SqlDataType == SqlType.Decimal)
                                        {
                                            columnSchema.Precision = (short)columnRow["NumericPrecision"];
                                            columnSchema.Scale = (short)columnRow["NumericScale"];
                                        }
                                        columnSchema.Table = tableSchema;

                                        tableSchema.Columns.Add(columnSchema);
                                    }
                                }
                            }
                        }

                        cachedSchema.Tables.Add(tableSchema);
                    }
                }

                string columnQuery = @"Select COLUMN_NAME, COLUMN_DEFAULT, TABLE_NAME from INFORMATION_SCHEMA.COLUMNS";

                // We cant get the default value information from the GetSchemaTable Methode, so we have
                // to do a second select for the default values.
                using (SqlCommand columnCommand = new SqlCommand(columnQuery, sqlConnection))
                {
                    using (SqlDataReader reader = columnCommand.ExecuteReader())
                    {
                        // Loop through each column.
                        while (reader.Read())
                        {
                            string columnName = (string)reader["COLUMN_NAME"];
                            string tableName = (string)reader["TABLE_NAME"];

                            // If the column has a default value.
                            if (reader["COLUMN_DEFAULT"] != DBNull.Value)
                            {
                                // Find out the column schema for this object
                                // in the database schema and set its default value.
                                ColumnSchema theColumn = cachedSchema.Tables[tableName].Columns[columnName];
                                theColumn.DefaultValue = (string)reader["COLUMN_DEFAULT"];

                                if (theColumn.DefaultValue.StartsWith("(("))
                                {
                                    theColumn.DefaultValue = theColumn.DefaultValue.Substring(2, theColumn.DefaultValue.IndexOf("))") - 2);
                                }
                            }
                        }
                    }
                }

                string primaryKeyQuery = @"select s.name as TABLE_SCHEMA, t.name as TABLE_NAME, k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE, c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION  
                    from sys.key_constraints as k 
                    join sys.tables as t on t.object_id = k.parent_object_id 
                    join sys.schemas as s on s.schema_id = t.schema_id  
                    join sys.index_columns as ic on ic.object_id = t.object_id and ic.index_id = k.unique_index_id  
                    join sys.columns as c on c.object_id = t.object_id and c.column_id = ic.column_id where k.type_desc = 'PRIMARY_KEY_CONSTRAINT' order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, ORDINAL_POSITION";

                using (SqlCommand primaryKeyCommand = new SqlCommand(primaryKeyQuery, sqlConnection))
                {
                    using (SqlDataReader reader = primaryKeyCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string columnName = (string)reader["COLUMN_NAME"];
                            string tableName = (string)reader["TABLE_NAME"];

                            ColumnSchema primaryKeyColumn = cachedSchema.Tables[tableName].Columns[columnName];
                            primaryKeyColumn.IsPrimaryKey = true;
                        }
                    }
                }

                string constraintsQuery = @"select 
                    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.TABLE_NAME, 
                    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.COLUMN_NAME, 
                    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.DELETE_RULE, 
                    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UPDATE_RULE, 
                    INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME, 
                    unique_usage.COLUMN_NAME 
                    from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 
                    inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS on INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.CONSTRAINT_NAME =  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME 
                    inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS on INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME 
                    inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE unique_usage on INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = unique_usage.CONSTRAINT_NAME";

                using (SqlCommand constraintsCmd = new SqlCommand(constraintsQuery, sqlConnection))
                {
                    using (SqlDataReader reader = constraintsCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string parentTableName = (string)reader[4];
                            TableSchema parentTable = cachedSchema.Tables[parentTableName];

                            string parentColumnName = (string)reader[5];
                            ColumnSchema parentColumn = parentTable.Columns[parentColumnName];

                            string childTableName = (string)reader[0];
                            TableSchema childTable = cachedSchema.Tables[childTableName];

                            string childColumnName = (string)reader[1];
                            ColumnSchema childColumn = childTable.Columns[childColumnName];

                            ReferenceSchema referenceSchema = null;
                            foreach (ReferenceSchema temp in parentTable.OutReferences)
                            {
                                if (temp.ChildTable == childTable && temp.ParentTable == parentTable)
                                {
                                    referenceSchema = temp;
                                }
                            }

                            if (referenceSchema == null)
                            {
                                referenceSchema = new ReferenceSchema(parentTable, childTable);
                                referenceSchema.OnDeleteCascade = reader[2].ToString().StartsWith("CASCADE");
                                referenceSchema.OnUpdateCascade = reader[3].ToString().StartsWith("CASCADE");

                                parentTable.OutReferences.Add(referenceSchema);

                                childTable.InReferences.Add(referenceSchema);
                            }

                            referenceSchema.Joins.Add(new ReferenceJoin(parentColumn, childColumn));
                        }
                    }
                }
            }

            return cachedSchema;
        }

        #endregion
    }
}
